﻿using System;
using System.Collections.Generic;
using System.Text;
using NHibernate;
using System.Data;
using BEGDC.Utility.NHibernate;
using Castle.Services.Transaction;
using System.Collections;
using System.Xml;
using Castle.Facilities.NHibernateIntegration;

namespace BEGDC.PreContract.BLL
{
    public partial class Email : IEmail
    {
        public enum ClassName
        {
            PreContractEmail
        }

        public DataTable GetEmailApplicant()
        {
            using (ISession session = factory.OpenSession())
            {
                string strSql = "SELECT b.EmailContent,b.subject,a.ApplicantEmail,a.Applicant "
                                + "FROM PreContractInfo as a inner join Email as b on a.PreContractID = b.PreContractID "
                                + "where b.IsSend = 0 group by a.ApplicantEmail,b.Subject,b.EmailContent,a.Applicant";
                DataTable dtSearch = DAO.ExecuteDataTable(session, strSql);

                return dtSearch;
            }
        }

        public DataTable GetEmailPreContractList(string applicantEmail, string subject, string content)
        {
            using (ISession session = factory.OpenSession())
            {
                string strSql = String.Format("SELECT a.PreContractID,b.EmailID "
                                + "FROM PreContractInfo as a inner join Email as b on a.PreContractID = b.PreContractID "
                                + "WHERE (a.ApplicantEmail = '{0}' OR a.ApplicantEmail Is NULL) AND b.Subject = N'{1}' AND b.EmailContent = N'{2}' AND b.IsSend = 0 "
                                +"GROUP by a.PreContractID,b.EmailID", applicantEmail, subject, content);
                DataTable dtSearch = DAO.ExecuteDataTable(session, strSql);

                return dtSearch;
            }
        }

        public DataTable GetEmailPrincipal()
        {
            using (ISession session = factory.OpenSession())
            {
                string strSql = "SELECT b.EmailContent,b.subject,a.PrincipalEmail,a.Principal "
                                + "FROM PreContractInfo as a inner join Email as b on a.PreContractID = b.PreContractID "
                                + "where b.IsSend = 0 group by a.PrincipalEmail,b.Subject,b.EmailContent,a.Principal";
                DataTable dtSearch = DAO.ExecuteDataTable(session, strSql);

                return dtSearch;
            }
        }

        public DataTable GetEmailPreContractListPrincipal(string principalEmail, string subject, string content)
        {
            using (ISession session = factory.OpenSession())
            {
                string strSql = String.Format("SELECT a.PreContractID,b.EmailID "
                                + "FROM PreContractInfo as a inner join Email as b on a.PreContractID = b.PreContractID "
                                + "WHERE a.PrincipalEmail = '{0}' AND b.Subject = N'{1}' AND b.EmailContent = N'{2}' AND b.IsSend = 0 "
                                + "GROUP by a.PreContractID,b.EmailID", principalEmail, subject, content);
                DataTable dtSearch = DAO.ExecuteDataTable(session, strSql);

                return dtSearch;
            }
        }
    }
}



